package com.xmy.cultivate.mapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.xmy.cultivate.entity.EnrollRoyaltySet;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

/**
 * <p>
 * 招生提成设置 Mapper 接口
 * </p>
 *
 * @author hpiggy
 * @since 2024-09-11
 */
public interface EnrollRoyaltySetMapper extends BaseMapper<EnrollRoyaltySet> {
    @Select("SELECT COUNT(*) FROM enroll_royalty_set WHERE opening_school_type = 1 AND level = ${level} AND (('${startDate}' BETWEEN start_date AND end_date) OR ('${endDate}' BETWEEN start_date AND end_date) OR (start_date BETWEEN '${startDate}' AND '${endDate}') OR (end_date BETWEEN '${startDate}' AND '${endDate}')) ")
    Integer countByDate(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate, @Param("level") Integer level);

    @Select("SELECT COUNT(*) FROM enroll_royalty_set AS t1 LEFT JOIN appoint_school AS t2 ON t2.for_id = t1.id  WHERE school_id = ${schoolId} AND t2.type = 6 AND t1.opening_school_type = 2 AND t2.deleted = 0 AND (('${startDate}' BETWEEN start_date AND end_date) OR ('${endDate}' BETWEEN start_date AND end_date) OR (start_date BETWEEN '${startDate}' AND '${endDate}') OR (end_date BETWEEN '${startDate}' AND '${endDate}')) ")
    Integer countAppointSchoolByDate(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate, @Param("schoolId") Long schoolId);

    @Select({"<script>",
            "SELECT * FROM (",
            "SELECT t1.*, ",
            "GROUP_CONCAT( DISTINCT getSchoolName ( t2.school_id ) SEPARATOR '、' )AS school_name,",
            "GROUP_CONCAT( DISTINCT t2.school_id SEPARATOR '、' )AS school_id_list",
            "FROM enroll_royalty_set AS t1 ",
            "LEFT JOIN appoint_school AS t2 ON t2.for_id = t1.id AND t2.type = 6 AND t2.deleted = 0 ",
            "WHERE 1 = 1 ",
            "<when test='startDate!=null and startDate!=&apos;&apos; and endDate!=null and endDate!=&apos;&apos;'> ",
            "AND ((t1.start_date BETWEEN '${startDate}' AND '${endDate}' ) OR ( t1.end_date BETWEEN '${startDate}' AND '${endDate}' ) OR ( t1.start_date &lt;= '${startDate}' AND t1.end_date &gt;= '${endDate}' )) ",
            "</when>",
            "<when test='schoolId!=null and schoolId!=&apos;&apos;'> ",
            "AND (t1.opening_school_type = 1 OR t2.school_id IN (${schoolId})) ",
            "</when>",
            "<when test='level!=null and level!=&apos;&apos;'> ",
            "AND t1.`level` = ${level} ",
            "</when>",
            "<when test='status!=null and status!=&apos;&apos;'> ",
                "<when test='status == 0'> ",
                    "AND t1.start_date &gt; curdate() ",
                "</when>",
                "<when test='status == 1'> ",
                    "AND ( t1.start_date &lt;= curdate() AND t1.end_date &gt;= curdate() ) ",
                "</when>",
                "<when test='status == 2'> ",
                    "AND t1.end_date &lt; curdate() ",
                "</when>",
            "</when>",
            "GROUP BY t1.id ",
            ") as t",
            "</script>"})
    IPage<EnrollRoyaltySet> getAll(IPage<?> page, @Param("startDate") String startDate, @Param("endDate") String endDate, @Param("schoolId") String schoolId, @Param("level") String level, @Param("status") String status);

}
